<?php
namespace app\admin\controller;
use app\service\roominfoService;
use app\service\waterService;
use think\facade\Db;

class Water extends Common{
    public function waterowner(){
        $community_id = request()->post('community_id');
        $group = request()->post('group');
        $building = request()->post('building');
        $unit = request()->post('unit');
        $room = request()->post('room');
        //查询业主信息
        $ownername = (new waterService())->ownerName($community_id,$group,$building,$unit,$room);
        //查询业主上期吨数
        $upperstage = (new waterService())->upperStage($community_id,$group,$building,$unit,$room,$ownername);
        if(empty($upperstage)){
            $upperstage = 0;
        }
        return json_encode([
            'ownername'   => $ownername,
            'upperstage'  => $upperstage
        ]);
    }
    public function waterList(){
        $com = $this->pidTokenId();
        $whe['community_id'] = array('in',$com['community_id']);
        $communitylist = (new roominfoService())->communityList($whe);
        $community_id = $com['community_id'];
        View::assign('communitylist',$communitylist);
        //搜索时
        $data = request()->get();
        if (!empty($data['community_id'])){
            $groups = array_unique((new roominfoService())->groupList($data['community_id']));
            View::assign('groups', array_values($groups));
                $buildings = array_unique((new roominfoService())->buildingList($data['community_id'],$data['group']));
                sort($buildings);
                View::assign('buildings',$buildings);
                if (!empty($data['building'])) {
                    $units = array_unique((new roominfoService())->unitList($data['community_id'], $data['group'],$data['building']));
                    sort($units);
                    View::assign('units',$units);
                    if(!empty($data['unit'])){
                        $rooms = array_unique((new roominfoService())->roomList($data['community_id'], $data['group'],$data['building'],$data['unit']));
                        sort($rooms);
                        View::assign('rooms',$rooms);
                    }
                }
        }
        if (!empty($data)){
            $where=array();
            if(!empty($data['community_id'])){
                $where['community_id'] = $data['community_id'];//小区名称
            }else{
                $where['community_id'] = "";
            }
            if(!empty($data['group'])){
                if(empty($data['building'])){
                    $where['room_address'] = array('like',$data['group'].'%');//完整门牌号
                }elseif(empty($data['unit'])){
                    $where['room_address'] = array('like',$data['group'].$data['building'].'%');//完整门牌号
                }elseif(empty($data['room'])){
                    $where['room_address'] = array('like',$data['group'].$data['building'].$data['unit'].'%');//完整门牌号
                }else{
                    $where['room_address'] = array('like',$data['group'].$data['building'].$data['unit'].$data['room'].'%');//完整门牌号
                }
            }else{
                if(empty($data['building'])){
                    $where['room_address'] = array('like','%');//完整门牌号
                }elseif(empty($data['unit'])){
                    $where['room_address'] = array('like',$data['building'].'%');//完整门牌号
                }elseif(empty($data['room'])){
                    $where['room_address'] = array('like',$data['building'].$data['unit'].'%');//完整门牌号
                }else{
                    $where['room_address'] = array('like',$data['building'].$data['unit'].$data['room'].'%');//完整门牌号
                }
            }
            if(!empty($data['status'])){
                $where['status'] = $data['status'];//账单状态
            }else{
                $where['status'] = "";
            }
            $where = array_filter($where);
        }else{
            $where=array();
        }
        $page = request()->has('page') ? request()->get('page') : 1;
        $export = $where;
        $export = json_encode($export);
        $export = str_replace("\"","|",$export);
        View::assign('export',$export);
        // dump($community);dump($page);dump($where);exit();
        //查询水费表数据
        $waterinfo = (new waterService())->waterWhereList($community_id,$page,$where);
        $number = (new waterService())->waterWhereListNumber($community_id,$where);
        View::assign('number',$number);
        if(!empty($number)){        
            foreach($waterinfo as $info){
                //根据小区编号查询小区名称
                $info['community_id'] = (new waterService())->communityName($info['community_id']);
                $water[] = $info;
            }
            View::assign('water',$water);
        }else{
            View::assign('water',$waterinfo);
        }   
        View::assign('waterinfo',$waterinfo);
        foreach ($data as $k => $v) {
            View::assign($k, $v);
        }
        return View::fetch('water_list');
    }
    public function waterAdd(){
        $com = $this->pidTokenId();
        $whe['community_id'] = array('in',$com['community_id']);
        $communitylist = (new roominfoService())->communityList($whe);
        $community_id = $com['community_id'];
        View::assign('communitylist',$communitylist);

        if(request()->isPost()){
            //获取表单数据
            $data = request()->post();
            //房屋完整门牌
            $data['room_address'] = $data['group']."-".$data['building']."-".$data['unit']."-".$data['room'];
            //根据小区名称，房间号查询是否已有数据
            $communityaddress = (new waterService())->waterCommunityAddress($data['community_id'],$data['room_address']);
            if(!empty($communityaddress)){
                //已有数据，更改数据
                $info['id'] = $communityaddress['id'];
                $info['upper_stage'] = $communityaddress['book_stage'];
                $info['book_stage'] = $data['book_stage'];
                $info['upper_use'] = $communityaddress['book_use'];
                //查询本期用量
                $info['book_use'] = $data['book_stage'] - $data['upper_stage'];
                $info['money'] = $info['book_use'] * $communityaddress['unit_price'];
                $info['status'] = 2;
                $info['time'] = date('Y-m-d H:i:s',time());
                //将数据修改数据库
                $result = (new waterService())->waterEdit($info);
            }else{
                //无数据，添加数据
                //查询本期用量
                $data['book_use'] = $data['book_stage'] - $data['upper_stage'];
                $data['upper_use'] = 0;
                $data['time'] = date('Y-m-d H:i:s',time());
                //根据小区名称查询电费单价
                $data['unit_price'] = (new waterService())->unitPrice($data['community_id']);
                //水费总金额
                $data['money'] = $data['book_use'] * $data['unit_price'];
               
                //根据当前登录账号查询抄表员
                $data['status'] = 2;
                $data['meter_reader'] = (new waterService())->meterReader(session('id'));
                //将数据插入数据库
                $result = (new waterService())->waterAdd($data);
            }
            //判断数据是否插入成功
            if($result){
                return "添加水费账单成功";
                $this->success('添加水费账单成功',url('waterAdd'));
            }else{
                $this->error('添加水费账单失败',url('waterAdd'));
            }
        }else{
            return View::fetch('water_add');
        }
    }
    public function waterBill(){
        $id = explode(',',request()->param('id'));
        $info = (new waterService())->waterListId($id);
        if (count($info) != count($id)) {
            return json(['error' => '水费信息不完整']);
        }
        foreach($info as $inf){
            if($inf['status'] == '2'){
                //生成业务标识
                $data['batch_id'] = date('YmdHis', time()).\util\Tool::getRandomStr(8);
                $data['community_id'] = $inf['community_id'];
                $data['bill_entry_id'] = date('YmdHis', time()).\util\Tool::getRandomStr(6);
                $data['out_room_id'] = (new waterService())->outroomid($inf['community_id'],$inf['room_address']);
                $data['room_address'] = $inf['room_address'];
                $data['cost_type'] = '水费';
                $data['bill_entry_amount'] = $inf['money'];
                $data['acct_period'] = '上期'.$inf['upper_stage'].'-本期'.$inf['book_stage'];
                $data['release_day'] = date('Ymd',time());
                $data['deadline'] = date('Ymd',strtotime('+5 years'));
                $data['tongbu'] = '2';
                $data['status'] = 'WAIT_PAYMENT';
                $data['time'] = date('Y-m-d H:i:s',time());
                $data['printing'] = '2';
                // dump($data);exit();
                //将数据传入账单
                $result = (new waterService())->waterBillAdd($data);
                $status = Db::name('water')->where('id',$inf['id'])->setField('status','1');  
            }else{
                return json(['error' => '已生成账单']);
            }
        }
        return json(['info' => '生成账单成功']);
    }
    public function waterDel(){
        $id = explode(',',request()->param('id'));        
        $info = (new waterService())->waterListId($id);
        if (count($info) != count($id)) {
            return json(['error' => '水费信息不完整']);
        }
        foreach($info as $inf){
            if($inf['status'] == 1){
                    return json(['error' => '已生成账单，不能删除']); 
            }else{
                $result = Db::name('water')->delete($inf['id']);
            }
        }
        return json(['info' => '删除成功']);  
    }

    //导入表格数据
    public function waterExcel(){
        if(request()->isPOST()){
            //上传excel文件
            $file = request()->file('myfile');
            //移到/public/excel/下
            $info = $file->move(ROOT_PATH.'public'.DS.'excel');
            //上传文件成功
            if ($info) {
                //引入PHPExcel类
                vendor('PHPExcel.PHPExcel.Reader.Excel2007');
                //获取上传后的文件名
                $fileName = $info->getSaveName();
                //文件路径
                $filePath = dir(getcwd())->path.'/excel/'.$fileName;
                //判断文件格式，实例化相应的PHPExcel类，读取excel文件
                $extension = strtolower( pathinfo($fileName, PATHINFO_EXTENSION));
                if ($extension =='xlsx' || $extension =='xls') {
                    $objReader = new \PHPExcel_Reader_Excel2007();
                    $objPHPExcel = $objReader ->load($filePath);
                } else if ($extension=='csv') {
                    $objReader = new \PHPExcel_Reader_CSV();
                    //默认输入字符集
                    $objReader->setInputEncoding('GBK');
                    //默认的分隔符
                    $objReader->setDelimiter(',');
                    //载入文件
                    $objPHPExcel = $objReader ->load($filePath);
                }else{
                    $this->success('文件格式错误',url('billExcel'));
                }  
                //读取excel文件中的第一个工作表
                $sheet = $objPHPExcel->getSheet(0);
                $allRow = $sheet->getHighestRow();  //取得总行数
                //$allColumn = $sheet->getHighestColumn();  //取得总列数
                //从第三行开始插入，第一行是列名
                for ($j=2; $j <= $allRow; $j++) {
                    $communityName = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();
                    //根据小区名称查找community_id
                    if(!empty($communityName)){
                        $communityList = (new waterService())->communityNameId($communityName);
                        if(!empty($communityList)){
                            $data['community_id'] = $communityList['community_id']; //小区ID
                        }else{
                            $data['community_id'] = '';
                        }  
                    }else{
                        $data['community_id'] = '';
                    }
                    $data['group'] = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();
                    $data['building'] = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();
                    $data['unit'] = $objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue();
                    $data['room'] = $objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue();
                   
                    $data['room_address'] =$data['group']."-".$data['building']."-".$data['unit']."-".$data['room'] ;//完整门牌号
                    $data['owner_name'] = $objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue();//业主姓名
                    $data['upper_stage'] = $objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue();//上期吨数
                    $data['book_stage'] = $objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue();//本期吨数
                    $data['upper_use'] = $objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue();//上期用量
                    $data['book_use'] = $objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue();//本期用量
                    $data['unit_price'] = $objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();//水费单价
                    $data['money'] = $objPHPExcel->getActiveSheet()->getCell("L".$j)->getValue();//总金额
                    $data['meter_reader'] = $objPHPExcel->getActiveSheet()->getCell("M".$j)->getValue();//抄表员
                    $data['status'] = 2;//状态
                    $data['time'] = $objPHPExcel->getActiveSheet()->getCell("K".$j)->getValue();//抄表时间
                    //去空
                    $data = array_filter($data);
                    //数量
                    $number = count($data);
                    $numbers = 15;
                    if($number >= $numbers && !empty($data['room_address'])){
                        //查询房屋号是否正确
                        $roominfo = (new waterService())->roomAddressInfo($data['community_id'],$data['room_address']);
                        if(empty($roominfo)){
                            echo "第".$j."行数据房号未同步支付宝，导入失败！<br/>";                            
                        }else{
                            //查询是否已有数据
                            $communityaddress = (new waterService())->waterCommunityAddress($data['community_id'],$data['room_address']);
                            if(empty($communityaddress)){
                                //未存在，直接添加
                                $water_id = (new waterService())->waterExcelAdd($data);
                                //判断数据是否插入成功
                                if ($water_id) {
                                    echo "第".$j."行导入成功，水表第:".$water_id."条！<br/>";
                                }else{
                                    echo "第".$j."行导入失败！<br/>";
                                }  
                            }else{
                                //已存在，修改数据
                                $water['id'] = $communityaddress['id'];
                                $water['upper_stage'] = $communityaddress['book_stage'];
                                $water['book_stage'] = $data['book_stage'];
                                $water['upper_use'] = $communityaddress['book_use'];
                                //查询本期用量
                                $water['book_use'] = $data['book_stage'] - $data['upper_stage'];
                                $water['money'] = $water['book_use'] * $data['unit_price'];
                                $water['status'] = 2;
                                $water['time'] = $data['time'];
                                $water_id = (new waterService())->waterEdit($water);
                                //判断数据是否插入成功
                                if ($water_id) {
                                    echo "第".$j."行导入成功，水费表第:".$water['id']."条！<br/>";
                                }else{
                                    echo "第".$j."行导入失败！<br/>";
                                }  
                            }
                        }
                    }else{
                        echo "第".$j."行数据不完整，导入失败！<br/>";
                    }
                }
            }else{
                $this->error('上传文件失败',url('waterExcel'));
            }
        }else{
            return View::fetch('water_excel');
        }
    }

    //导出水费数据
    public function waterExportExcel(){
        //获取账单数据
        $id = request()->param('id');
        $export = request()->param('export');
        $export = str_replace("|","\"",$export);
        // dump($id);dump($export);exit();
        if(!empty($id)){
            $id = explode(',',request()->param('id'));
            $waterList = (new waterService())->waterListId($id);
            if (count($waterList) != count($id)) {
                return json(['info' => '账单信息不完整']);
            }
        }else{
            $where = json_decode($export,true);
            $waterList = (new waterService())->waterWhereExport($where);
            if(empty($waterList)){
                return json(['info' => '没有账单数据']);
            }
        }
        //设置表头
        //引入PHPExcel类
        vendor('PHPExcel.PHPExcel.Reader.Excel2007');
        $objPHPExcel = new \PHPExcel();
        //设置表头
        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'ID')
        ->setCellValue('B1', '小区名称')
        ->setCellValue('C1', '完整门牌号')
        ->setCellValue('D1', '业主姓名')
        ->setCellValue('E1', '上期吨数')
        ->setCellValue('F1', '本期吨数')
        ->setCellValue('G1', '上期用量')
        ->setCellValue('H1', '本期用量')
        ->setCellValue('I1', '水费单价')
        ->setCellValue('J1', '总金额')
        ->setCellValue('K1', '抄表员')
        ->setCellValue('L1', '同步状态')      
        ->setCellValue('M1', '抄表时间');

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($waterList);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $waterList[$i-2]["id"]);
            $community_name = (new waterService())->communityIdName($waterList[$i-2]["community_id"]);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $community_name);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $waterList[$i-2]["room_address"]);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $waterList[$i-2]["owner_name"]);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $waterList[$i-2]["upper_stage"]);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $waterList[$i-2]["book_stage"]);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $waterList[$i-2]["upper_use"]);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $waterList[$i-2]["book_use"]);
            $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $waterList[$i-2]["unit_price"]);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $waterList[$i-2]["money"]);
            $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $waterList[$i-2]["meter_reader"]);
            //判断账单状态
            if($waterList[$i-2]["status"] == "1"){
                $status = '已生成账单';
            }elseif($waterList[$i-2]["status"] == "2"){
                $status = '未生成账单';
            }
            $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $status);
            $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $waterList[$i-2]["time"]);
        }
        $objPHPExcel->getActiveSheet()->setTitle('水费表信息');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        header('Content-Disposition: attachment;filename="水费表信息.xls"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007"); //通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
    }
}